package com.sqlcheck.service.impl;

import com.sqlcheck.config.LLMConfiguration;
import com.sqlcheck.dto.request.LLMOptimizeRequest;
import com.sqlcheck.dto.response.LLMApiResponse;
import com.sqlcheck.dto.response.LLMConfigResponse;
import com.sqlcheck.dto.response.LLMOptimizationResult;
import com.sqlcheck.entity.CompatibilityRule;
import com.sqlcheck.service.CompatibilityRuleService;
import com.sqlcheck.service.LLMApiService;
import com.sqlcheck.service.LLMOptimizationService;
import com.sqlcheck.service.PromptGeneratorService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CompletableFuture;

@Service
@RequiredArgsConstructor
@Slf4j
public class LLMOptimizationServiceImpl implements LLMOptimizationService {

    private final LLMConfiguration llmConfiguration;
    private final LLMApiService llmApiService;
    private final PromptGeneratorService promptGeneratorService;
    private final CompatibilityRuleService compatibilityRuleService;

    @Override
    public LLMOptimizationResult optimizeSql(LLMOptimizeRequest request, Long userId, String requestIp,
            String userAgent) {
        log.info("=== 开始SQL优化任务 ===");
        log.info("用户ID: {}, 请求IP: {}, User-Agent: {}", userId, requestIp, userAgent);
        log.info("数据库类型: {} -> {}", request.getSourceDbType(), request.getTargetDbType());
        log.info("原始SQL长度: {} 字符", request.getSql().length());
        if (log.isDebugEnabled()) {
            log.debug("原始SQL内容:\n{}", request.getSql());
        }
        if (request.getRules() != null && !request.getRules().isEmpty()) {
            log.info("兼容性规则数量: {}", request.getRules().size());
            if (log.isDebugEnabled()) {
                log.debug("兼容性规则: {}", request.getRules());
            }
        }

        try {
            // 1. 获取兼容性规则
            log.info("步骤1: 获取数据库兼容性规则...");
            List<CompatibilityRule> dbRules = compatibilityRuleService.getRulesForLLMOptimization(
                    request.getSourceDbType(), request.getTargetDbType());
            log.info("获取到 {} 条兼容性规则", dbRules.size());

            // 2. 生成提示词
            log.info("步骤2: 生成优化提示词...");
            String prompt = promptGeneratorService.buildPrompt(
                    request.getSourceDbType(),
                    request.getTargetDbType(),
                    request.getSql(),
                    dbRules);

            log.info("生成的提示词长度: {} 字符", prompt.length());
            if (log.isDebugEnabled()) {
                log.debug("完整提示词内容:\n{}", prompt);
            }

            // 3. 使用系统配置的LLM设置
            log.info("步骤3: 准备LLM配置...");
            LLMOptimizeRequest.LLMConfig llmConfig = createDefaultLLMConfig();
            log.info("使用LLM配置 - 提供商: {}, 模型: {}, Temperature: {}, MaxTokens: {}",
                    llmConfig.getProvider(), llmConfig.getModelName(),
                    llmConfig.getTemperature(), llmConfig.getMaxTokens());

            // 4. 调用LLM API
            log.info("步骤4: 调用LLM API...");
            LLMApiResponse apiResponse = llmApiService.callLLM(prompt, llmConfig);

            if (!apiResponse.isSuccess()) {
                log.error("LLM API调用失败: {}", apiResponse.getErrorMessage());
                return createErrorResult(request.getSql(), "LLM API调用失败: " + apiResponse.getErrorMessage());
            }

            log.info("LLM API调用成功！");
            log.info("API响应 - 提供商: {}, 模型: {}, Token使用: {}, 处理时间: {}ms",
                    apiResponse.getProvider(), apiResponse.getModelName(),
                    apiResponse.getTokensUsed(), apiResponse.getProcessingTime());

            // 5. 解析响应并构建结果
            log.info("步骤5: 解析API响应并构建结果...");
            LLMOptimizationResult result = parseApiResponse(request.getSql(), apiResponse);

            log.info("SQL优化完成！");
            log.info("结果概要 - 原始SQL: {} 字符, 优化SQL: {} 字符, 高亮数: {}, 问题数: {}",
                    result.getOriginalSql().length(),
                    result.getOptimizedSql().length(),
                    result.getHighlights().size(),
                    result.getIssues().size());
            log.info("=== SQL优化任务完成 ===");

            return result;

        } catch (Exception e) {
            log.error("SQL优化过程中发生错误", e);
            log.error("错误详情: {}", e.getMessage());
            log.info("=== SQL优化任务失败 ===");
            return createErrorResult(request.getSql(), "优化过程中发生错误: " + e.getMessage());
        }
    }

    @Override
    public CompletableFuture<LLMOptimizationResult> optimizeSqlAsync(LLMOptimizeRequest request, Long userId) {
        return CompletableFuture.supplyAsync(() -> optimizeSql(request, userId, null, null));
    }

    @Override
    public LLMConfigResponse getLLMConfig(Long userId) {
        LLMConfigResponse.LLMConfigResponseBuilder builder = LLMConfigResponse.builder()
                .enabled(llmConfiguration.isEnabled())
                .defaultProvider(llmConfiguration.getDefaultProviderName())
                .providers(new ArrayList<>());

        // 添加费率限制信息
        if (llmConfiguration.getRateLimit() != null) {
            builder.dailyLimit(llmConfiguration.getRateLimit().getRequestsPerDay())
                    .minuteLimit(llmConfiguration.getRateLimit().getRequestsPerMinute());
        }

        builder.todayUsage(0);

        return builder.build();
    }

    @Override
    public boolean testLLMConnection(LLMOptimizeRequest.LLMConfig config) {
        try {
            return llmApiService.testConnection(config);
        } catch (Exception e) {
            log.error("测试LLM连接失败", e);
            return false;
        }
    }

    @Override
    public boolean checkUsageLimit(Long userId) {
        return true;
    }

    @Override
    public int getTodayUsage(Long userId) {
        return 0;
    }

    /**
     * 创建默认LLM配置
     */
    private LLMOptimizeRequest.LLMConfig createDefaultLLMConfig() {
        LLMOptimizeRequest.LLMConfig config = new LLMOptimizeRequest.LLMConfig();
        config.setProvider(llmConfiguration.getDefaultProviderName());

        LLMConfiguration.ProviderConfig providerConfig = llmConfiguration.getDefaultProviderConfig();
        if (providerConfig != null) {
            config.setModelName(providerConfig.getDefaultModel());
            config.setTemperature(providerConfig.getTemperature());
        }

        return config;
    }

    /**
     * 解析API响应
     */
    private LLMOptimizationResult parseApiResponse(String originalSql, LLMApiResponse apiResponse) {
        String content = apiResponse.getContent();
        log.info("开始解析LLM响应内容，总长度: {} 字符", content.length());

        try {
            // 解析结构化响应
            ParsedLLMResponse parsed = parseStructuredResponse(content);

            log.info("解析结果 - 优化SQL长度: {}, 优化说明长度: {}",
                    parsed.optimizedSql.length(), parsed.optimizationDetails.length());

            // 构建结果
            return LLMOptimizationResult.builder()
                    .originalSql(originalSql)
                    .optimizedSql(parsed.optimizedSql)
                    .explanation(parsed.explanation)
                    .optimizationDetails(parsed.optimizationDetails)
                    .highlights(new ArrayList<>())
                    .issues(new ArrayList<>())
                    .metadata(LLMOptimizationResult.OptimizationMetadata.builder()
                            .provider(apiResponse.getProvider())
                            .modelName(apiResponse.getModelName())
                            .tokensUsed(apiResponse.getTokensUsed())
                            .processingTime(apiResponse.getProcessingTime())
                            .confidence(0.85) // 示例置信度
                            .build())
                    .build();

        } catch (Exception e) {
            log.error("解析LLM响应失败，使用原始内容", e);

            // 解析失败时的降级处理
            return LLMOptimizationResult.builder()
                    .originalSql(originalSql)
                    .optimizedSql(content)
                    .explanation("LLM响应解析失败，显示原始内容")
                    .optimizationDetails("")
                    .highlights(new ArrayList<>())
                    .issues(new ArrayList<>())
                    .metadata(LLMOptimizationResult.OptimizationMetadata.builder()
                            .provider(apiResponse.getProvider())
                            .modelName(apiResponse.getModelName())
                            .tokensUsed(apiResponse.getTokensUsed())
                            .processingTime(apiResponse.getProcessingTime())
                            .confidence(0.5) // 降级时置信度较低
                            .build())
                    .build();
        }
    }

    /**
     * 解析结构化LLM响应
     */
    private ParsedLLMResponse parseStructuredResponse(String content) {
        log.debug("解析结构化响应内容");

        // 使用正则表达式提取优化后SQL和优化说明
        String optimizedSql = extractOptimizedSql(content);
        String optimizationDetails = extractOptimizationDetails(content);
        String explanation = "基于兼容性规则进行的SQL优化";

        if (optimizedSql.trim().isEmpty()) {
            log.warn("未能提取到优化后的SQL，使用原始内容");
            optimizedSql = content;
        }

        return new ParsedLLMResponse(optimizedSql, explanation, optimizationDetails);
    }

    /**
     * 提取优化后的SQL
     */
    private String extractOptimizedSql(String content) {
        try {
            // 查找 "## 优化后SQL" 部分
            String sqlPattern = "##\\s*优化后SQL\\s*```sql\\s*([\\s\\S]*?)```";
            java.util.regex.Pattern pattern = java.util.regex.Pattern.compile(sqlPattern,
                    java.util.regex.Pattern.CASE_INSENSITIVE);
            java.util.regex.Matcher matcher = pattern.matcher(content);

            if (matcher.find()) {
                String extractedSql = matcher.group(1).trim();
                log.debug("成功提取优化后SQL，长度: {} 字符", extractedSql.length());

                // 清理SQL注释，保留核心SQL
                return cleanOptimizedSql(extractedSql);
            }

            // 如果没找到标准格式，尝试查找任何SQL代码块
            String fallbackPattern = "```sql\\s*([\\s\\S]*?)```";
            pattern = java.util.regex.Pattern.compile(fallbackPattern, java.util.regex.Pattern.CASE_INSENSITIVE);
            matcher = pattern.matcher(content);

            if (matcher.find()) {
                String extractedSql = matcher.group(1).trim();
                log.debug("使用降级模式提取SQL，长度: {} 字符", extractedSql.length());
                return cleanOptimizedSql(extractedSql);
            }

            log.warn("未能从响应中提取SQL代码块");
            return content;

        } catch (Exception e) {
            log.error("提取优化SQL时发生错误", e);
            return content;
        }
    }

    /**
     * 提取优化说明
     */
    private String extractOptimizationDetails(String content) {
        try {
            // 查找 "## 优化说明" 部分
            String detailsPattern = "##\\s*优化说明\\s*([\\s\\S]*?)(?=##|$)";
            java.util.regex.Pattern pattern = java.util.regex.Pattern.compile(detailsPattern,
                    java.util.regex.Pattern.CASE_INSENSITIVE);
            java.util.regex.Matcher matcher = pattern.matcher(content);

            if (matcher.find()) {
                String extractedDetails = matcher.group(1).trim();
                log.debug("成功提取优化说明，长度: {} 字符", extractedDetails.length());

                // 转换Markdown表格为HTML表格格式，便于前端显示
                String htmlTable = convertMarkdownTableToHtml(extractedDetails);
                return htmlTable;
            }

            log.debug("未找到标准的优化说明部分");
            return "";

        } catch (Exception e) {
            log.error("提取优化说明时发生错误", e);
            return "";
        }
    }

    /**
     * 将Markdown表格转换为HTML表格格式
     */
    private String convertMarkdownTableToHtml(String markdownTable) {
        try {
            String[] lines = markdownTable.split("\n");
            StringBuilder htmlBuilder = new StringBuilder();
            boolean inTable = false;
            boolean hasHeader = false;

            for (String line : lines) {
                line = line.trim();

                // 跳过空行和非表格行
                if (line.isEmpty()) {
                    continue;
                }

                // 检查是否是表格行（包含 | 分隔符）
                if (line.contains("|")) {
                    // 跳过分隔线
                    if (line.matches("^\\|[\\s\\-\\|]+\\|$")) {
                        continue;
                    }

                    // 开始表格
                    if (!inTable) {
                        htmlBuilder.append(
                                "<table class='optimization-table' style='width:100%; border-collapse: collapse; margin-top: 16px;'>\n");
                        inTable = true;
                    }

                    // 解析表格行
                    String[] cells = line.split("\\|");
                    if (cells.length >= 2) {
                        // 处理表头
                        if (!hasHeader) {
                            htmlBuilder.append("<thead>\n<tr style='background-color: #fafafa; font-weight: bold;'>\n");
                            for (int i = 1; i < cells.length - 1; i++) {
                                String cellContent = cells[i].trim();
                                htmlBuilder.append(
                                        "<th style='border: 1px solid #d9d9d9; padding: 8px 12px; text-align: left;'>")
                                        .append(cellContent)
                                        .append("</th>\n");
                            }
                            htmlBuilder.append("</tr>\n</thead>\n<tbody>\n");
                            hasHeader = true;
                        } else {
                            // 处理数据行
                            htmlBuilder.append("<tr>\n");
                            for (int i = 1; i < cells.length - 1; i++) {
                                String cellContent = cells[i].trim();

                                // 根据列的内容应用不同的样式
                                String cellStyle = "border: 1px solid #d9d9d9; padding: 8px 12px;";

                                // 为影响程度列添加颜色标识
                                if (i == cells.length - 2) { // 最后一列是影响程度
                                    if (cellContent.equals("高")) {
                                        cellStyle += " background-color: #fff1f0; color: #cf1322;";
                                    } else if (cellContent.equals("中")) {
                                        cellStyle += " background-color: #fffbe6; color: #d48806;";
                                    } else if (cellContent.equals("低")) {
                                        cellStyle += " background-color: #f6ffed; color: #389e0d;";
                                    }
                                }

                                // 为修改类型列添加样式
                                if (i == 2) { // 修改类型列
                                    cellStyle += " font-weight: 500;";
                                }

                                htmlBuilder.append("<td style='").append(cellStyle).append("'>")
                                        .append(cellContent)
                                        .append("</td>\n");
                            }
                            htmlBuilder.append("</tr>\n");
                        }
                    }
                } else {
                    // 非表格行，关闭表格
                    if (inTable) {
                        htmlBuilder.append("</tbody>\n</table>\n");
                        inTable = false;
                        hasHeader = false;
                    }

                    // 添加其他内容（如说明文字）
                    if (line.startsWith("**") && line.endsWith("**")) {
                        // 处理粗体文字
                        String boldText = line.substring(2, line.length() - 2);
                        htmlBuilder.append("<p style='font-weight: bold; margin: 16px 0 8px 0; color: #262626;'>")
                                .append(boldText)
                                .append("</p>\n");
                    } else if (line.matches("^\\d+\\..*")) {
                        // 处理编号列表
                        htmlBuilder.append("<p style='margin: 4px 0; color: #595959; font-size: 14px;'>")
                                .append(line)
                                .append("</p>\n");
                    } else {
                        // 普通文字
                        htmlBuilder.append("<p style='margin: 8px 0; color: #595959;'>")
                                .append(line)
                                .append("</p>\n");
                    }
                }
            }

            // 确保表格闭合
            if (inTable) {
                htmlBuilder.append("</tbody>\n</table>\n");
            }

            String result = htmlBuilder.toString();
            log.debug("转换Markdown表格为HTML，结果长度: {} 字符", result.length());
            return result;

        } catch (Exception e) {
            log.error("转换Markdown表格为HTML时发生错误", e);
            // 返回原始内容
            return markdownTable;
        }
    }

    /**
     * 清理优化后的SQL，移除不必要的注释
     */
    private String cleanOptimizedSql(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return sql;
        }

        // 移除开头的注释行
        String[] lines = sql.split("\n");
        StringBuilder cleaned = new StringBuilder();
        boolean foundSql = false;

        for (String line : lines) {
            String trimmedLine = line.trim();

            // 跳过纯注释行，但保留包含SQL的行
            if (trimmedLine.startsWith("--") && !foundSql) {
                continue;
            }

            // 保留SQL语句和修改标记
            if (!trimmedLine.isEmpty()) {
                cleaned.append(line).append("\n");
                if (!trimmedLine.startsWith("--") && !trimmedLine.startsWith("/*")) {
                    foundSql = true;
                }
            }
        }

        return cleaned.toString().trim();
    }

    /**
     * 解析结果封装类
     */
    private static class ParsedLLMResponse {
        final String optimizedSql;
        final String explanation;
        final String optimizationDetails;

        ParsedLLMResponse(String optimizedSql, String explanation, String optimizationDetails) {
            this.optimizedSql = optimizedSql;
            this.explanation = explanation;
            this.optimizationDetails = optimizationDetails;
        }
    }

    /**
     * 创建错误结果
     */
    private LLMOptimizationResult createErrorResult(String originalSql, String errorMessage) {
        return LLMOptimizationResult.builder()
                .originalSql(originalSql)
                .optimizedSql(originalSql)
                .explanation("优化失败: " + errorMessage)
                .optimizationDetails("")
                .highlights(new ArrayList<>())
                .issues(new ArrayList<>())
                .metadata(LLMOptimizationResult.OptimizationMetadata.builder()
                        .provider("error")
                        .modelName("error")
                        .confidence(0.0)
                        .build())
                .build();
    }
}